演習２：実際にSQLで操作してみよう

演習2.1. SQLite3 ハンズオン

まずはデータを準備しましょう。空港コードをSQLite3のデータベースで公開しているリポジトリがあるので、これを利用しましょう。

code: bash

$ curl -sLO https://github.com/mihi-tr/Airport-Data/raw/master/data/db/airports.sqlite

せっかくなので python でダウンロードしてみましょう。

request を使うのでインストールしておきます。

code: bash

$ pip install requests

code: download.py

import requests

base_url = 'https://github.com/mihi-tr/Airport-Data'

url = base_url + '/raw/master/data/db/airports.sqlite'

response = requests.get(url)

with open('airports.sqlite', 'wb') as f:

f.write(response.content)

演習2.1.1: SQLiteデータベースに接続してみる

ダウンロードしたデータベースを開いてみましょう。

手順は sqlite3 モジュールをインポートして、connect()関数にデータベースのDSNを与えます。

このとき、ダウンロードしたデータベースがカレントディレクトリに airport.sqlite としてあるとき、

どのように指定すればよいか試してみましょう。

code: ex_connect.py

import sqlite3

DNS="xxxxxxxxxxxxxxxxxxxx" # ここを適宜修正してください

conn = sqlite3.connect(DSN)

演習2.1.2 テーブルを確認しよう

自分で設計したデータベースであれば、データベースがどうのように定義されているかは自明ですが、今回は、明確になっているのはこのデータベースがSQLite3 で作成されたものだということだけです。

データベースに接続して定義内容を確認するところからはじめましょう。

手順は、テータベースに接続した接続オブジェクト(conn)のcursor()メソッドを呼び出して、カーソルオブジェクトを生成します。このカーソルオブジェクトにあるexecute()メソッドに SQL文を与えます。

code: ex_table.py

from ex_connect import *

QUERY="xxxxxxxxxxxxxxxxxx" # ここを適宜修正してください

c = conn.cursor()

c.execute(QUERY)

data = c.fetchall()

print(data)

演習2.1.3 テーブルのカラムを確認しよう

データベースのテーブルがどう定義されているかを確認しましょう。

Python のコード自体は、テーブルを確認する手順とほとんど同じで、SQL文だけが異なります。

code: ex_column.py

from ex_connect import *

QUERY="xxxxxxxxxxxxxxxxxx" # ここを適宜修正してください

c.execute(QUERY)

data = c.fetchall()

print(data)

演習2.1.4 レコード数を確認しよう

code: ex_count.py

from ex_connect import *

QUERY="xxxxxxxxxxxxxxxxxx" # ここを適宜修正してください

c.execute(QUERY)

data = c.fetchall()

print(data)

ヒント

SELECT カラム名 FROM テーブル名;の結果からレコード数をカウントすることができます。

COUNT(カラム名)を使うことができます。

演習2.1.5

レコード数を求める場合、SQLコマンドのCOUNT()を使う方が効率的です。この理由を考えてみましょう。

演習2.1.6 空港のデータを取得しよう

EX1: 関西国際空港の情報を取得してみましょう

EX2: 日本の空港がいくつあるか調べてみましょう

ヒント

空港コードは iataカラムに入っています。

空港コードは "KIX" です。

演習 2.2. sqlite-utils ハンズオン

演習2.2.1 テーブルを確認しよう

演習2.1 で使用した空港コードを格納したデータベース airports.sqlite を使って、テーブルを確認してみましょう。

EX1: データベースにあるテーブルを確認してみましょう

EX2: それぞれのテーブルのスキーマを確認してみましょう

演習 2.2.2 データベースを操作

SQLite3のデータベース airports.sqlite を操作して次のことを調べてみましょう。

EX1: 関西国際空港の情報を取得してみましょう

EX2: 日本の空港がいくつあるか調べてみましょう

EX2: 日本の空港を抽出してCSVファイルにしてみましょう

参考資料

AirPort Data